Top100 School Districts

1 NCES

1.1 Top SD in NCES

• Excluding 5 US territories: “AS”, “DC”, “GU”, “PR”, “VI”
• Take 105 top sd in NCES list because could not find acfrs for 4 of the first 100

2 School districts from Acfrs database 2020, 2021

• Data queried from database on Feb 21, 2023.
• There are 35526 acfrs collected in 2020 and 34441 in 2021. Of which, the numbers of school districts acfrs are 9669 and 10333 for 2020 and 2021, respectively.

2.1 Top SD in ACFRs

Found 93 acfrs that are in the top 100 largest of NCES.

SD in the top 100 NCES that are not collected in the current ACFRs database

2.2 New York City

NYC DOE reports total numbers of 32 school districts. https://infohub.nyced.org/docs/default-source/default-document-library/2021-annual-financial-statements.pdf Page 26, 33

Method: Apportion Acfrs data for School Districts - Calculate the students share of each school district. - Multiply this share to the DOE total.

# input number from DOE report 
nyc_2020 <- nces %>% filter(str_detect(nces_original_name, "(?i)NEW YORK CITY GEOGRAPHIC DISTRICT")) %>% 
#year 2020 - Department of Education NYC
  mutate(year = 2020, 
         doe_total_liabilities = 59391179000, #page 33
         doe_net_opeb = 35457858000, #page 33
         doe_net_pension = 0, #(Geoff checked, no net pension)
         doe_expenses = 30782114000,# page 26
         doe_total_asset = 66355469000, #page 33
         doe_revenues = 28104219000)   #page 26
  
nyc_2021 <- nces %>% filter(str_detect(nces_original_name, "(?i)NEW YORK CITY GEOGRAPHIC DISTRICT")) %>% 
#year 2021 - Department of Education NYC
  mutate(year = 2021,
         doe_total_liabilities = 48619741000, #page 33
         doe_net_opeb = 38982221000,#page 33
         doe_net_pension = -1137314, # page 33 (Geoff: adding - due to over funding, other than this, prob only state Wisconsin) 
         doe_expenses = 29278093000,# page 26
         doe_total_asset = 66152289000, #page 33
         doe_revenues = 28976372000) #page 26

nyc_20_21 <- rbind(nyc_2020, nyc_2021)
  
  nyc_20_21 %>% 
    # students share
  mutate(
  id = NA, 
  share_student = students/sum(students),
  
  # apportion financial data for all 32 sd
  total_liabilities = doe_total_liabilities*share_student,
  net_opeb_liability = doe_net_opeb*share_student,
  net_pension_liability = doe_net_pension*share_student,
  expenses = doe_expenses*share_student,
  revenues = doe_revenues*share_student) %>% 
  # 
  select(ncesID, nces_original_name, year, county_name, city, state, id, students, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues) %>% 
  
# only get 5 of those who are in top100 largest
filter(ncesID %in% top_sd_acfrs_10$ncesID) -> nyc_20_21_5sd

2.3 Boston

• It looks like Boston Public Schools finances are consolidated with the city. They city’s FY21 ACFR is here: ACFR_01.31.22_FINAL.pdf (boston.gov). It does not provide discreet reporting for schools, so we’ll have to make some assumptions.

• Total FY21 expenses were $2,083,935,000 and program revenues total to $568,994,000 (p. 20). It lists FTE employees at 10,149 (9,528+621) in FY21 (p. 150). That’s 53.82% of total city FTEs.

• Citywide pension liability is $1,406,402,000 and OPEB is $2,196,724,000. If we apply that ratio, the calculated pension liability is $756,926,000 and OPEB is $1,182,277,000. We can’t calculate anything else.

2.3.1 Boston 2021

fte_employee_share = .5382 #FTE employees at 10,149 (9,528+621) in FY21 (p. 150)

boston_city_2021 <- readRDS("data/data_from_dbsite_2021.RDS") %>% 
  filter(category == "General Purpose" & name == "Boston" & state == "MA") %>% 
  mutate(id = as.character(id)) %>% 
  select(state, name,id, year, total_liabilities, net_pension_liability, net_opeb_liability, expenses, revenues)

boston_2021 <- nces %>% filter(nces_original_name == "Boston" & state == "MA") %>% 
mutate(
  id = NA, 
  year = 2021,
  
    # apportion sd from city
  total_liabilities = 0, # can't do this city_total_liability * fte_employee_share
  net_opeb_liability = boston_city_2021$net_opeb_liability*fte_employee_share,
  net_pension_liability =  boston_city_2021$net_pension_liability*fte_employee_share,
  
  # manual insert
  expenses = 2083935000, #schools line, page 20
  revenues = 539271000 + 18727000 + 10996000) %>% # sum of 3 cols program revenues, school line, p20, school district run by the city
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

2.3.2 Boston 2020

##### 2020
fte_employee_share = .5382 #10149

boston_city_2020 <- readRDS("data/data_from_dbsite_2020.RDS") %>% 
  filter(category == "General Purpose" & name == "Boston" & state == "MA") %>% 
  mutate(id = as.character(id)) %>% 
  select(state, name,id, year, total_liabilities, net_pension_liability, net_opeb_liability, expenses, revenues)

boston_2020 <- nces %>% filter(nces_original_name == "Boston" & state == "MA") %>% 
mutate(
  id = NA, 
  year = 2020,
  
   # apportion sd from city
  total_liabilities = 0, # can't do this: city_total_liability * fte_employee_share
  net_opeb_liability = boston_city_2020$net_opeb_liability*fte_employee_share,
  net_pension_liability =  boston_city_2020$net_pension_liability*fte_employee_share,
  
  # manual insert
  expenses = 1874077000, #schools line, page A-17, school line
  revenues = 9444000 + 60527000 + 10200000) %>% ## sum of 3 cols program revenues, school line, page A-17
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

2.4 Chesterfield

Chesterfield County schools are consolidated with the county, but reported as a component unit: 762 (chesterfield.gov). - Expenses and revenues can be found on p. 227. - Balance sheet items are on p. 224. - Net pension liability is broken out on p. 169.

chesterfield_2021 <- nces %>% filter(nces_original_name == "CHESTERFIELD CO PBLC SCHS" & state == "VA") %>%
mutate(
  id = NA, 
  total_assets = 122955915, #page 224
  year = 2021,
  total_liabilities = 62869374,#page 224
  net_opeb_liability = 0,## How about page 179, Schedule of changes in Net OPEB?? -> have both 2020, 2021
  net_pension_liability = 0, #Geoff: should use the net pension liability on p. 169. That seems to focus on teachers.
  #OR: should be this number??? 
  #net_pension_liability = 75809615, - page 174
  
  expenses = 709920047, # #Geoff: For revenues and expenses, use the values on p. 225; those on p. 227 are just a subcomponent.  
  revenues = 704737890) %>% 
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

#######

chesterfield_2020 <- nces %>% filter(nces_original_name == "CHESTERFIELD CO PBLC SCHS" & state == "VA") %>%
mutate(
  id = NA, 
  total_assets = 121514512, # page 224, Schedule C-1, Discretely presented Component Unit- School board
  year = 2020,
  total_liabilities = 63252560, # page 224,Schedule C-1, Discretely presented Component Unit- School board
  net_opeb_liability = 0,
  
  net_pension_liability = 1223857, #page 169, school board component unit, column 2020, Chesterfield ACFRS 2021 OR should be page 174: Program total pension liability ending 2020: 76,292,808 & 2021: 74,809,615
  
  expenses = 659875816, # page 225, column School operating, Schedule C-2, Discretely presented Component Unit- School board - Statement of Revenues, Expenditure and changes in Fund Balance
  revenues = 658178834) %>% # page 225,
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

2.5 Nashville

Nashville schools are consolidated with Davidson County: ACFRFY21_01_21_2022_Upload.pdf (nashville.gov). Balance sheet items are on p. B-6 and should be the sum of columns called “General Purpose School,” “Education Services,” and “GSD School Purposes Debt Service.” Revenues and expenditures are on p. B-10 and includes the same columns.

• We can apportion OPEB as we did for Boston. On p. H-35, the employee headcounts show 9,055 out of 18,548 are in education. That’s 48.8%. Total liabilities are listed on p. B-142.

–> Page B-143 shows “school professional employees’

• For pensions, they’re showing a net asset (it’s overfunded) on p. B-140. It looks like there are two plans, so we should add those together: $56,699,625 + $5,853,778 = $62,553,403.

employee_share = .4848
county_opep_liabilities_2021 = 3240451063 # p. B-142.
county_opep_liabilities_2020 = 3064106607 # p. B-142.

davidson_county_2021 <- nces %>% filter(nces_original_name == "Davidson County" & state == "TN") %>%
mutate(
  id = NA, 
  total_assets = 649639619 + 118474605 + 124590923, # sum of columns called "General Purpose School," "Education Services," and "GSD School Purposes Debt Service."
  year = 2021,
  total_liabilities = 44660790 + 89820946 + 212989, # sum of above 3 columns 
  net_opeb_liability = county_opep_liabilities_2021*employee_share,
  
  #OR use page B-143 school professional employees
  #net_opeb_liability = 1061732808,
  
  net_pension_liability = 0, 
  expenses = 758516604 + 363357551 + 108450334, #B-10
  revenues = 982397940 + 213091395 + 138244239) %>% # page B-10
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

##### year 2020

davidson_county_2020 <- nces %>% filter(nces_original_name == "Davidson County" & state == "TN") %>%
mutate(
  id = NA, 
  total_assets = 56699625 + 5853778, #For pensions, they're showing a net asset (it's overfunded) on p. B-140. It looks like there are two plans, so we should add those together: 
  year = 2020,
  total_liabilities = 44660790 + 89820946 + 212989, # sum of above 3 columns 
  net_opeb_liability = county_opep_liabilities_2020*employee_share,
  
  #OR use page B-143 school professional employees
  #net_opeb_liability = 1196937989,
  
  net_pension_liability = -(56699625 + 5853778), #?? 
  expenses = NA, 
  revenues = NA) %>% 
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

2.6 Henrico

Henrico County schools are a component unit of Henrico County: Henrico-County-VA-FY21-ACFR-Final.pdf. page 25 - 26

henrico_2021 <- nces %>% filter(nces_original_name == "HENRICO CO PBLC SCHS" & state == "VA") %>%
mutate(
  id = NA, 
  total_assets = 384459131, 
  year = 2021,
  total_liabilities = 650708572,
  net_opeb_liability = 76925442,
  net_pension_liability = 520851160,
  expenses = 614221032, 
  revenues = 268922094) %>% # page 26, line total general revenues
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

######

henrico_2020 <- nces %>% filter(nces_original_name == "HENRICO CO PBLC SCHS" & state == "VA") %>%
mutate(
  id = NA, 
  total_assets = 393231084,
  year = 2020,
  total_liabilities = 602815832,
  net_opeb_liability = 81756137,
  net_pension_liability = 462836320,
  expenses = 574231130, 
  revenues = 211589891) %>% #page 26, line total general revenues
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

2.7 Delaware

Delaware has school district info in the state ACFR: FY 2021 ACFR (delaware.gov). Balance sheet info is on p. 166-167 and activities are on p. 168-169. –> manually inserted these numbers to excel file. –> Where to find net pension, net OPEB for K12? (State of Delaware in page 20) –> Don’t do this yet. Let me figure out why school districts are only listed in the statistical section–I’m not sure whether the liabilities are consolidated with the state’s.

# readRDS("data/data_from_dbsite_2021.RDS") %>% 
#   filter(str_detect(name, "State of Delaware")) %>% 
#   filter(category == "General Purpose")

2.8 Davidson County TN & Portland SD 1J OR

sd_96_2020 <- top_sd_nces %>% 
  left_join(top_sd_acfrs) %>% # to get id
  left_join(sd2020_db, by = "id") %>% # need to specify join by=id, if not, some rows in sd2020_db loose values (eg. id == 196828) 
  
  select(-c(name.y, name.x, state.y)) %>% 
  rename(state = state.x) %>% 

  # take out sd whose acfrs are not known to date
  filter(!nces_original_name %in% c("Boston", "CHESTERFIELD CO PBLC SCHS", "HENRICO CO PBLC SCHS", "OMAHA PUBLIC SCHOOLS")) %>% 
  
  # take out 5 sd of NYC to later rbind with nyc_20_21_5sd
  filter(state != "NY") %>% 
  
  # Collect data for some sd manually 
  mutate(year = 2020,
  total_liabilities = case_when(nces_original_name == "Davidson County"& state == "TN" ~ 0,
                                nces_original_name == "Portland SD 1J"& state == "OR" ~ 1882236000, 
                                TRUE ~ total_liabilities), #page 34
  
  net_pension_liability = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 0,
                                    nces_original_name == "Portland SD 1J"& state == "OR" ~ 306140000, 
                                    TRUE ~ net_pension_liability), # page 34, Net pension liability-PERS
  
  net_opeb_liability = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 0,
                                 nces_original_name == "Portland SD 1J"& state == "OR" ~ 81319999,
                                 TRUE ~ net_opeb_liability), # page 34, TOTAL OPEB liability-RHIS
  
  expenses = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 0,
                       nces_original_name == "Portland SD 1J"& state == "OR" ~ 824384000, 
                       TRUE ~ expenses),#page 35
                       
  
  revenues = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 0,
                       nces_original_name == "Portland SD 1J"& state == "OR" ~ (8271000 + 90072000 + 11368000 + 823400000), #page 35 (charges + operating + grant + Total general revenues)
                       TRUE ~ revenues))

sd_96_2021 <- top_sd_nces %>% 
  left_join(top_sd_acfrs) %>% # to get id
  left_join(sd2021_db, by = "id") %>% # NOTE: filter(is.na(total_liabilities)) -> some have id carried from 2020, but no values for 2021
  select(-c(name.y, name.x, state.y)) %>% 
  rename(state = state.x) %>%  
  
  # take out sd whose acfrs are not known to date
  filter(!nces_original_name %in% c("Boston", "CHESTERFIELD CO PBLC SCHS", "HENRICO CO PBLC SCHS", "OMAHA PUBLIC SCHOOLS")) %>% 
  
  # take out 5 sd of NYC to later rbind with nyc_20_21_5sd
  filter(state != "NY") %>% 
  
  mutate(year = 2021,
  total_liabilities = case_when(nces_original_name == "Davidson County"& state == "TN" ~ 0,
                                nces_original_name == "Portland SD 1J"& state == "OR" ~ 2380580000,
                                TRUE ~ total_liabilities),
  
  net_pension_liability = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 84041613,
                                    nces_original_name == "Portland SD 1J"& state == "OR" ~ 521329000,
                                    TRUE ~ net_pension_liability), # page 32, Net pension liability-PERS
  
  net_opeb_liability = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 1061732808, # page B-93, column School professional employee
                                 nces_original_name == "Portland SD 1J"& state == "OR" ~ 78581000,
                                 TRUE ~ net_opeb_liability), # page 32, TOTAL OPEB liability-RHIS
  
  expenses = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 1195361854,
                       nces_original_name == "Portland SD 1J"& state == "OR" ~ 931870000,
                       TRUE ~ expenses), #page 33
  
  revenues = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 488401680,
                       nces_original_name == "Portland SD 1J"& state == "OR" ~ (1914000 + 113835000 + 1319000 + 822868000), # Geoff: need to take sum 4 of these: charges, operating, capital grant and general revenues)
                       TRUE ~ revenues) #page 33
)

2.9 VA Fairfax 2021

fairfax_2021 <- nces %>% 
  filter(nces_original_name == "FAIRFAX CO PBLC SCHS" & state == "VA") %>%
mutate(
  id = 200059, 
  total_assets = 3446600000, #p 10, Summary of Net position
  year = 2021,
  total_liabilities = 4923543994, #p 21, Statement of Net position
  net_opeb_liability = 387461319, #p 21,  
  net_pension_liability = 4115292996, #p 21
  expenses = 3320812299, #p22 Statement of activities
  revenues = 76892323 + 411859293 + 194679133 + 2718687903) %>% #p 22, 
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

76892323 + 411859293 + 194679133 + 2718687903

## [1] 3402118652

2.10 VA Loudoun 2021

loudoun_2021 <- nces %>% 
  filter(nces_original_name == "LOUDOUN CO PBLC SCHS" & state == "VA") %>%
mutate(
  id = 197654, 
  total_assets = 2608200000, #p 11, Summary of Net position
  year = 2021,
  total_liabilities = 1810600000, #p 11, Summary of Net position
  net_opeb_liability = 334800000, #p 17, Outstanding long-term liabilities 
  net_pension_liability = 1185300000, #p 17, Outstanding long-term liabilities
  expenses = 1541300000, 
  revenues = 1611300000) %>% #p 11, Summary of changes in net position
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

2.11 VA Prince William 2021

princewilliam_2021 <- nces %>% 
  filter(nces_original_name == "PRINCE WILLIAM CO PBLC SCHS" & state == "VA") %>%
mutate(
  id = 197665, 
  total_assets = 2445510537, #p 34, Statement of Net position
  year = 2021,
  total_liabilities = 1376448983, #p 11, Summary of Net position
  net_opeb_liability = 142765589, #p 38, Reconciliation of the Balance Sheet of the Gov funds
  net_pension_liability = 1003741236, #p 38, Reconciliation of the Balance Sheet of the Gov funds
  expenses = 1376884937, #p 35, statement of activities, line school division
  revenues = 5561927 + 280176028 + 127346 + 1268840518) %>% #p35 statement of activities, line school division --> sum of 3 columns program revenues --> 2020 = sum of 3 cols + col total school division X line Total general revenues and transfers?
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

2.12 VA BEACH CITY PBLC SCHS 2021

vabeach_2021 <- nces %>% 
  filter(nces_original_name == "VA BEACH CITY PBLC SCHS" & state == "VA") %>%
mutate(
  id = 197671, 
  total_assets = 899445665, #p 234, Discretely presented school board - Statement of Net position; column Governmental activities
  year = 2021,
  total_liabilities = 1077086065, #p 234
  net_opeb_liability = 147744317, #p 234
  net_pension_liability = 748825120, #p 234
  expenses = 881553759, #p 235, statement of activities, line total governmental activities
  revenues = 3408114 + 201162593 + 0 + 706048089) %>% #p235, sum of program revenues + total general revenues, statement of activities, line school division --> Similar to 2020: the revenues 985726201 = sum of 4 cols + total general rev?
  # 
  select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)

3 Top 100 largest school districts by enrollments 2020, 2021

3.1 Top 100 largest school districts count by state

4 Per Student

4.1 Total Liabilities per Student

4.2 Net Pension Liabilities per Student

4.3 Net OPEB Liabilities per Student

5 Total Liabilities-to-Revenues Ratio

5.1 Plot

The majority of SD has total liability to revenues ratio larger than 1 (points below the diagonal line).

5.2 Table

top100_result %>% 
  select(1:2,5, liability_rev_ratio) %>% 
  arrange(liability_rev_ratio) -> ratio

datatable(ratio)

6 Top 10 largest SD by enrollment

6.1 Percentage changes 2020-2021 in each category: Table

• Increases in most categories.

• Palm Beach FL has a steep decrease in net OPEB liability (66% decrease) & a large increase in net pension liability (30% increase)

6.2 Percentage changes 2020-2021 in each category: Plot

6.3 Total Assets-to-Total Liabilities Ratio

6.4 Total Liabilities-to-Revenues Ratio